if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsNSeedAgrFund_HR_POAP_GL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsNSeedAgrFund_HR_POAP_GL]
GO

/***************************************************************************
* Name:			spFdsNSeedAgrFund_HR_POAP_GL
* Purpose:		Using one or more staging tables, seed table AgrFund with
*				Fund Numbers that don't yet exist. Typically these will be
*				operational funds not necessarily connected to agreements
*				in Rex.
*
* PARAMETERS
* Name				Description
* -------------		-----------------------------------------------------------
* @feedFlag			Integer that indicates which staging table(s) should be
*					examined, with values as follows:
*
* Package Name				Param Value			Tables Examined
* ------------				-----------			---------------
* PerPersonnel_HR_Load			1				Stg_PerEmployeeDistribution
*
* PurVendor_POAP_Load			2				Stg_PurPODistribution_Src,
*												Stg_PurVoucherAccountingLine_Src,
*												Stg_PurVoucherDistribution_Src
*
* FinJournalTransaction_Load	3				Stg_JournalTransaction
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.spFdsNSeedAgrFund_HR_POAP_GL
(
	@feedFlag	int
)
AS
BEGIN

	DECLARE	@_entitySeed		int
	DECLARE	@_sql				varchar(1000)

	SET NOCOUNT ON

	-- create temp table for distinct new fund numbers
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpNewFundNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[tmpNewFundNumbers]

	CREATE TABLE dbo.tmpNewFundNumbers
	(
		FundNumber				varchar(10)	,
		SvcEntityId				int			,
		EffectiveDate			datetime	,
		BusinessUnit			varchar(5)	,
		BusinessUnitId			int			,
		AgrFundStatusTypeId		int			,
		AgrFundTypeId			int			,
		AgrFundSubTypeId		int			,
		FinAccountCycleTypeId	int			,
		AgrFundAwardTypeId		int			,
		OvrhdClassId			int
	)

	-- populate temp table based on parameter passed in
	IF	@feedFlag = 1		-- PerPersonnel_HR_Load
	BEGIN
		INSERT	tmpNewFundNumbers
					(
						FundNumber, SvcEntityId, EffectiveDate, BusinessUnit, BusinessUnitId,
						AgrFundStatusTypeId, AgrFundTypeId, AgrFundSubTypeId, FinAccountCycleTypeId,
						AgrFundAwardTypeId, OvrhdClassId
					)
		SELECT			EarnFundNumber AS FundNumber, 0 AS SvcEntityId, MAX(EarnEffectiveDate) AS EffectiveDate,
						MIN(EarnBusinessUnit) AS BusinessUnit,
						0 AS BusinessUnitId,		-- updated below
						1 AS AgrFundStatusTypeId,	-- status = Active
						2 AS AgrFundTypeId,			-- type = Operational Fund
						0 AS AgrFundSubTypeId,		-- updated below
						3 AS FinAccountCycleTypeId,	-- acct cycle = Project
						4 AS AgrFundAwardTypeId,	-- award type = Misc/Other
						31 AS OvrhdClassId			-- for now this is hardcoded to FundCode 'UNKNOWN'
		FROM			Stg_PerEmployeeDistribution
		LEFT OUTER JOIN	AgrFund
			ON				Stg_PerEmployeeDistribution.EarnFundNumber = AgrFund.FundNumber
		WHERE			Stg_PerEmployeeDistribution.EarnFundNumber IS NOT NULL
		AND				LTRIM(RTRIM(Stg_PerEmployeeDistribution.EarnFundNumber)) <> ''
		AND				AgrFund.Id IS NULL
		GROUP BY		EarnFundNumber
		ORDER BY		EarnFundNumber
	END
	ELSE
	IF	@feedFlag = 2		-- PurVendor_POAP_Load
	BEGIN
		-- Stg_PurPODistribution_Src
		SELECT			sProjectGrantNumber AS FundNumber, MAX(ModifiedDate) AS EffectiveDate, 
						MIN(sBusinessUnit) AS BusinessUnit
		INTO			#tmp1
		FROM			Stg_PurPODistribution_Src
		LEFT OUTER JOIN	AgrFund
			ON				Stg_PurPODistribution_Src.sProjectGrantNumber = AgrFund.FundNumber
		WHERE			Stg_PurPODistribution_Src.sProjectGrantNumber IS NOT NULL
		AND				LTRIM(RTRIM(Stg_PurPODistribution_Src.sProjectGrantNumber)) <> ''
		AND				AgrFund.Id IS NULL
		GROUP BY		sProjectGrantNumber

		UNION

		-- Stg_PurVoucherAccountingLine_Src
		SELECT			sProjectGrantNumber AS FundNumber, MAX(ModifiedDate) AS EffectiveDate,
						MIN(sBusinessUnit) AS BusinessUnit
		FROM			Stg_PurVoucherAccountingLine_Src
		LEFT OUTER JOIN	AgrFund
			ON				Stg_PurVoucherAccountingLine_Src.sProjectGrantNumber = AgrFund.FundNumber
		WHERE			Stg_PurVoucherAccountingLine_Src.sProjectGrantNumber IS NOT NULL
		AND				LTRIM(RTRIM(Stg_PurVoucherAccountingLine_Src.sProjectGrantNumber)) <> ''
		AND				AgrFund.Id IS NULL
		GROUP BY		sProjectGrantNumber

		UNION

		-- Stg_PurVoucherDistribution_Src
		SELECT			sProjectGrantNumber AS FundNumber, MAX(ModifiedDate) AS EffectiveDate,
						MIN(sBusinessUnit) AS BusinessUnit
		FROM			Stg_PurVoucherDistribution_Src
		LEFT OUTER JOIN	AgrFund
			ON				Stg_PurVoucherDistribution_Src.sProjectGrantNumber = AgrFund.FundNumber
		WHERE			Stg_PurVoucherDistribution_Src.sProjectGrantNumber IS NOT NULL
		AND				LTRIM(RTRIM(Stg_PurVoucherDistribution_Src.sProjectGrantNumber)) <> ''
		AND				AgrFund.Id IS NULL
		GROUP BY		sProjectGrantNumber
		ORDER BY		FundNumber

		-- now populate the temp table to be used by the rest of the procedure
		INSERT	tmpNewFundNumbers
					(
						FundNumber, SvcEntityId, EffectiveDate, BusinessUnit, BusinessUnitId,
						AgrFundStatusTypeId, AgrFundTypeId, AgrFundSubTypeId, FinAccountCycleTypeId,
						AgrFundAwardTypeId, OvrhdClassId
					)
		SELECT			FundNumber, 0 AS SvcEntityId, MAX(EffectiveDate) AS EffectiveDate,
						MIN(BusinessUnit) AS BusinessUnit,
						0 AS BusinessUnitId,		-- updated below
						1 AS AgrFundStatusTypeId,	-- status = Active
						2 AS AgrFundTypeId,			-- type = Operational Fund
						0 AS AgrFundSubTypeId,		-- updated below
						3 AS FinAccountCycleTypeId,	-- acct cycle = Project
						4 AS AgrFundAwardTypeId,	-- award type = Misc/Other
						31 AS OvrhdClassId			-- for now this is hardcoded to FundCode 'UNKNOWN'
		FROM			#tmp1
		GROUP BY		FundNumber
		ORDER BY		FundNumber

		DROP TABLE #tmp1
	END
	ELSE
	IF	@feedFlag = 3		-- FinJournalTransaction_Load
	BEGIN
		INSERT	tmpNewFundNumbers
					(
						FundNumber, SvcEntityId, EffectiveDate, BusinessUnit, BusinessUnitId,
						AgrFundStatusTypeId, AgrFundTypeId, AgrFundSubTypeId, FinAccountCycleTypeId,
						AgrFundAwardTypeId, OvrhdClassId
					)
		SELECT			sProjectGrantNumber AS FundNumber, 0 AS SvcEntityId, MAX(dtJournal) AS EffectiveDate,
						MIN(sBusinessUnit) AS BusinessUnit,
						0 AS BusinessUnitId,		-- updated below
						1 AS AgrFundStatusTypeId,	-- status = Active
						2 AS AgrFundTypeId,			-- type = Operational Fund
						0 AS AgrFundSubTypeId,		-- updated below
						3 AS FinAccountCycleTypeId,	-- acct cycle = Project
						4 AS AgrFundAwardTypeId,	-- award type = Misc/Other
						31 AS OvrhdClassId			-- for now this is hardcoded to FundCode 'UNKNOWN'
		FROM			Stg_JournalTransaction
		LEFT OUTER JOIN	AgrFund
			ON				Stg_JournalTransaction.sProjectGrantNumber = AgrFund.FundNumber
		WHERE			Stg_JournalTransaction.sProjectGrantNumber IS NOT NULL
		AND				LTRIM(RTRIM(Stg_JournalTransaction.sProjectGrantNumber)) <> ''
		AND				AgrFund.Id IS NULL
		GROUP BY		sProjectGrantNumber
		ORDER BY		sProjectGrantNumber
	END
	ELSE
	BEGIN
		RAISERROR('Unexpected parameter value passed to spFdsNSeedAgrFund_HR_POAP_GL', 16, 1)
		RETURN -1
	END

	-- get IDs for all the FKs
	-- fund sub-type Id where FundTypeId = 2 "Operational Fund"
	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 2
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,3) = AgrFundSubType.FundNumberPrefix

	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 2
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,2) = AgrFundSubType.FundNumberPrefix
	AND			tmpNewFundNumbers.AgrFundSubTypeId = 0

	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 2
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,1) = AgrFundSubType.FundNumberPrefix
	AND			tmpNewFundNumbers.AgrFundSubTypeId = 0

	-- fund sub-type Id where FundTypeId = 1 "Research Fund"
	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,3) = AgrFundSubType.FundNumberPrefix
	AND			tmpNewFundNumbers.AgrFundSubTypeId = 0

	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,2) = AgrFundSubType.FundNumberPrefix
	AND			tmpNewFundNumbers.AgrFundSubTypeId = 0

	UPDATE		tmpNewFundNumbers
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(tmpNewFundNumbers.FundNumber,1,1) = AgrFundSubType.FundNumberPrefix
	AND			tmpNewFundNumbers.AgrFundSubTypeId = 0

	-- TBD: define overhead classification(s) for these funds
	-- overhead classification Id
-- 	UPDATE		tmpNewFundNumbers
-- 	SET			OvrhdClassId = AgrFundOverheadClassification.Id
-- 	FROM		AgrFundOverheadClassification
-- 	WHERE		tmpNewFundNumbers.sFundCode = AgrFundOverheadClassification.PSCode

	-- business unit Id
	UPDATE		tmpNewFundNumbers
	SET			BusinessUnitId = AdmBusinessUnit.Id
	FROM		AdmBusinessUnit
	WHERE		LTRIM(RTRIM(tmpNewFundNumbers.BusinessUnit)) = LTRIM(RTRIM(AdmBusinessUnit.PSCode))

	-- delete records for which one of the required IDs was not found
	DELETE		tmpNewFundNumbers
	WHERE		AgrFundSubTypeId	= 0
	OR			OvrhdClassId		= 0
	OR			BusinessUnitId		= 0

	-- prepare to seed table SvcEntity
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededSvcIDs')
		DROP TABLE tmpSeededSvcIDs

	IF (SELECT Count(Id) FROM SvcEntity) = 0
		SET	@_entitySeed		= 1
	ELSE
		SET	@_entitySeed		= IDENT_CURRENT('SvcEntity') + 1

	-- seed new SvcEntity IDs
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_entitySeed AS varchar) + ', 1) AS SvcId, FundNumber INTO dbo.tmpSeededSvcIDs FROM tmpNewFundNumbers'
	EXEC(@_sql)

	UPDATE	tmpNewFundNumbers
	SET		SvcEntityId = tmpSeededSvcIDs.SvcId
	FROM	tmpSeededSvcIDs
	WHERE	tmpNewFundNumbers.FundNumber = tmpSeededSvcIDs.FundNumber

	DROP TABLE tmpSeededSvcIDs

	-- Now, insert the seeded Id records into tables
	-- SvcEntity
	SET IDENTITY_INSERT SvcEntity ON

	INSERT	SvcEntity (Id, SvcEntityTypeId)
	SELECT	SvcEntityId, 3
	FROM	tmpNewFundNumbers
	WHERE	SvcEntityId > 0

	SET IDENTITY_INSERT SvcEntity OFF

	-- AgrFund
	INSERT		AgrFund (
					SvcEntityId, FinAccountCycleTypeId, FundNumber, AgrFundStatusTypeId, 
					AgrFundAwardTypeId, AgrFundOverheadClassificationId, BusinessUnitId, RexOwned, 
					ActivationDate, AgrFundTypeId, AgrFundSubTypeId, EffectiveDate
					)
	SELECT			SvcEntityId, FinAccountCycleTypeId, FundNumber, AgrFundStatusTypeId, 
					AgrFundAwardTypeId, OvrhdClassId, BusinessUnitId, 0, 
					GetDate(), 2, AgrFundSubTypeId, EffectiveDate
	FROM		tmpNewFundNumbers
	WHERE		SvcEntityId > 0

	DROP TABLE tmpNewFundNumbers

	SET NOCOUNT OFF

END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
